﻿using System;
using System.IO;
using System.Collections.Generic;
using System.Collections;
using System.Text;

using EdgeStatz.ESSystems.Objects;

namespace EdgeStatz.ModelEngine.DatabaseTools
{
    public class DBStructureEngine
    {
        private DatabaseToolsXml xmlTools;

        public DBStructureEngine(string xmlPath)
        {
            SetXMLPath(xmlPath);
        }

        public void SetXMLPath(string xmlPath)
        {
            xmlTools = new DatabaseToolsXml(xmlPath);
        }

        public string DatabaseName
        {
            get { return xmlTools == null ? null : xmlTools.DatabaseName; }
            set
            {
                if (xmlTools != null)
                    xmlTools.DatabaseName = value;
            }
        }
        public string DatabasePath
        {
            get { return xmlTools == null ? null : xmlTools.DatabasePath; }
            set
            {
                if (xmlTools != null)
                    xmlTools.DatabasePath = value;
            }
        }

        public string ModelName
        {
            get { return xmlTools == null ? null : xmlTools.ModelName; }
            set
            {
                if (xmlTools != null)
                    xmlTools.ModelName = value;
            }
        }

        public string ModelPath
        {
            get { return xmlTools == null ? null : xmlTools.ModelPath; }
            set
            {
                if (xmlTools != null)
                    xmlTools.ModelPath = value;
            }
        }

        #region Structure Methods
        
        public string[] GetTableNames()
        {
            return xmlTools.GetTableNames();
        }

        public TableDefinition GetTableByName(string name)
        {
            return xmlTools.GetTable(name);
        }
        #endregion

        #region Writer Methods
        public TableDefinition[] GetTableDefinitions()
        {
            return xmlTools.Tables;
        }

        //public void AddTableDefinition(TableDefinition value)
        //{
        //    xmlWriter.AddTableDefinition(value);
        //}

        //public void ReplaceAllTableDefinitions(TableDefinition[] value)
        //{
        //    xmlWriter.ClearTableDefinitions();
        //    foreach (TableDefinition td in value)
        //        xmlWriter.AddTableDefinition(td);
        //}

        //public void ReplaceTableDefinition(TableDefinition value, int index)
        //{
        //    xmlWriter.ReplaceTableDefinition(value, index);
        //}

        //public void ReplaceTableDefinition(TableDefinition value, string name)
        //{
        //    TableDefinition[] array = xmlWriter.TableArray;
        //    for (int i = 0; i < array.Length; i++)
        //    {
        //        if (array[i].Name.ToUpper().Equals(name.ToUpper()))
        //        {
        //            xmlWriter.ReplaceTableDefinition(value, i);
        //            return;
        //        }
        //    }
        //}

        public void SaveChanges() 
        { 
            xmlTools.SaveFile();
            SetXMLPath(xmlTools.XmlFilePath);
        }

        public void CancelChanges() { SetXMLPath(xmlTools.XmlFilePath); }
        #endregion

        #region Create
        /// <summary>
        /// Returns a create database Sql query.
        /// </summary>
        /// <returns></returns>
        public string CreateDatabase()
        {
            if (xmlTools == null) { throw new NullReferenceException("xmlReader object is null"); }

            string dbName = xmlTools.DatabaseName;
            string dbPath = xmlTools.DatabasePath;

            int i = 0;
            while (File.Exists(dbPath + "\\" + dbName + (i > 0 ? "(" + i + ")" : "") + ".mdf") ||
                File.Exists(dbPath + "\\" + dbName + (i > 0 ? "(" + i + ")" : "") + "Log.ldf"))
            {
                i++;
            }

            return"CREATE DATABASE " + dbName + " ON PRIMARY " +
                        "(NAME = " + dbName +
                        ", FILENAME = '" + dbPath + "\\" + dbName + (i > 0 ? "(" + i + ")" : "") + ".mdf" + "'" +
                        ", SIZE = 5MB " +
                        ", MAXSIZE = 100MB " +
                        ", FILEGROWTH = 10% " +
                        ")" +
                        " LOG ON " +
                        "(NAME = " + dbName + "_Log " +
                        ", FILENAME = '" + dbPath + "\\" + dbName + (i > 0 ? "(" + i + ")" : "") + "Log.ldf' " +
                        ", SIZE = 1MB " +
                        ", MAXSIZE = 10MB " +
                        ", FILEGROWTH = 10% " +
                        ")";
        }

        public void AttachDatabase(string filepath)
        {
            string dbName = xmlTools.DatabaseName;
            if (filepath != null && !filepath.Equals("") && !CheckDatabaseExists(dbName))
            {   
                GlobalObjectsSQL.DatabaseName = "master";
                try
                {
                    GlobalObjectsSQL.ExecuteNonQueryCommand("CREATE DATABASE " + dbName +
                        " ON PRIMARY (FILENAME = '" + filepath + "')" +
                        " FOR ATTACH");
                }
                catch (Exception ex) 
                {
                    // throw error or warning back?
                }
                GlobalObjectsSQL.DatabaseName = dbName;
            }
        }
        /// <summary>
        /// Loads Tables to the database based on the schema from an xml file.
        /// </summary>
        public string[] CreateTables() 
        {
            if (xmlTools == null) { throw new NullReferenceException("xmlReader object is null"); }

            TableDefinition[] tables = xmlTools.Tables;

            List<string> foreignKeys = new List<string> { };
            List<string> foreignKeysSQL = new List<string> { };

            List<string> returnString = new List<string> { };

            foreach (TableDefinition tb in tables)
            {
                if (!GlobalObjectsSQL.CheckObjectExists(tb.Name, "USER_TABLE"))
                {
                    List<string> primaryKeys = new List<string> { };

                    string sqlQuery = "CREATE TABLE [dbo].[" + tb.Name + "](";
                    foreach(ColumnDefinition col in tb.ColumnArray)
                    {
                        sqlQuery += "[" + col.Name + "]";
                        switch (col.Type)
                        {
                            case ColumnType.GUID:
                                sqlQuery += " [uniqueidentifier]";
                                break;
                            case ColumnType.Varchar:
                                sqlQuery += " [varchar](" + col.Length + ") COLLATE Latin1_General_CI_AS";
                                break;
                            case ColumnType.Bit:
                                sqlQuery += " [bit]";
                                break;
                            case ColumnType.Datetime:
                                sqlQuery += " [datetime]";
                                break;
                            case ColumnType.Float:
                                sqlQuery += " [float]";
                                break;
                            case ColumnType.Int:
                                sqlQuery += " [int]";
                                break;
                        }
                        if (col.DefaultValue != null)
                        {
                            string def_val;
                            if (!col.DefaultValue.Trim().ToUpper().Equals("NEWID()"))
                                def_val = "'" + col.DefaultValue + "'";
                            else
                                def_val = col.DefaultValue;
                            sqlQuery += " CONSTRAINT [DF_" + tb.Name + "_" + col.Name +
                                    "]  DEFAULT (" + def_val + ")";
                        }
                        sqlQuery += col.Nullable ? " NULL": " NOT NULL";
                        if (col.PrimaryKey)
                            primaryKeys.Add(col.Name);
                        if(col.ForeignKey != null)
                        {
                            foreignKeys.Add("FK_" + tb.Name + "_" + col.Name + "_" + col.ForeignKey.Table + "_" + col.ForeignKey.Column);
                            foreignKeysSQL.Add("ALTER TABLE [dbo].[" + tb.Name +
                                        "] WITH CHECK ADD  CONSTRAINT [FK_" + tb.Name + "_" + col.Name +
                                        "_" + col.ForeignKey.Table + "_" + col.ForeignKey.Column + "] FOREIGN KEY([" + col.Name + "])" +
                                        " REFERENCES [dbo].[" + col.ForeignKey.Table + "] ([" + col.ForeignKey.Column + "])" +
                                        " GO " + "ALTER TABLE [dbo].[" + tb.Name +
                                        "] CHECK CONSTRAINT [FK_" + tb.Name + "_" + col.Name +
                                        "_" + col.ForeignKey.Table + "_" + col.ForeignKey.Column + "]");
                        }
                        sqlQuery += ",";
                    }
                    if (primaryKeys.Count != 0)
                    {
                        sqlQuery += " CONSTRAINT [PK_" + tb.Name + "] PRIMARY KEY CLUSTERED (";
                        for (int i = 0; i < primaryKeys.Count; i++)
                        {
                            sqlQuery += "[" + primaryKeys[i] + "] ASC";
                            if ((i + 1) < primaryKeys.Count)
                                sqlQuery += ",";
                        }
                        sqlQuery += ") WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF" +
                        ", IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, " +
                        "ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]";
                    }

                    sqlQuery += ") ON [PRIMARY]";
                    returnString.Add(sqlQuery);
                }
            }
            for (int j = 0; j < foreignKeys.Count; j++)
            {
                if (!GlobalObjectsSQL.CheckObjectExists(foreignKeys[j], "FOREIGN_KEY_CONSTRAINT"))
                    returnString.Add(foreignKeysSQL[j]);
            }
            return returnString.ToArray();
        }
        #endregion

        #region Delete
        public bool DeleteDatabase()
        {
            if (xmlTools == null) { throw new NullReferenceException("xmlReader object is null"); }

            string dbName = xmlTools.DatabaseName;
            GlobalObjectsSQL.DatabaseName = "master";
            GlobalObjectsSQL.ExecuteNonQueryCommand("Drop DATABASE " + dbName);

            return !CheckDatabaseExists(dbName);
        }

        public bool DetachDatabase()
        {
            if (xmlTools == null) { throw new NullReferenceException("xmlReader object is null"); }

            string dbName = xmlTools.DatabaseName;
            GlobalObjectsSQL.DatabaseName = "master";
            GlobalObjectsSQL.ExecuteNonQueryCommand("sp_detach_db " + dbName);

            return !CheckDatabaseExists(dbName);
        }
        #endregion

        #region Validate
        /// <summary>
        /// Checks if the database already exists.
        /// </summary>
        /// <param name="dbName">Name of the database to check</param>
        /// <returns>True if database exists; False otherwise</returns>
        public bool CheckDatabaseExists(string dbName) 
        {
            string cmdText = "select * from master.dbo.sysdatabases where name=\'" +dbName + "\'";
            string dbn = GlobalObjectsSQL.DatabaseName;
            GlobalObjectsSQL.DatabaseName = null;
            object nRet = GlobalObjectsSQL.ExecuteScalarQueryCommand(cmdText);
            GlobalObjectsSQL.DatabaseName = dbn;
            if (nRet == null) { return false; }
            else { return true; }
        }
        public bool CheckDatabaseExists()
        { return CheckDatabaseExists(DatabaseName); }

        /// <summary>
        /// Validates the current database against the model.
        /// </summary>
        /// <param name="xmlPath">Path to XML model file</param>
        /// <returns>String array containing differences between the database and model. 
        /// \nNull if model tables all exist and contain the same columns</returns>
        public string[] ValidateDatabaseAgainstModel() 
        {
            if (xmlTools == null) { throw new NullReferenceException("xmlReader object is null"); }

            List<string> returnString = new List<string>{};
            TableDefinition[] tables = xmlTools.Tables;

            if (!CheckDatabaseExists(xmlTools.DatabaseName))
            {
                returnString.Add("Database [" + xmlTools.DatabaseName + "] Doesn't exist.");
                return returnString.ToArray();
            }

            foreach(TableDefinition tb in tables)
            {
                string tableName = tb.Name;
                ColumnDefinition[] columns = tb.ColumnArray;
                
                ArrayList columnNameDB = new ArrayList();
                ArrayList columnTypeDB = new ArrayList();
                ArrayList nullableDB = new ArrayList();

                object[] reader = GlobalObjectsSQL.ExecuteReaderQueryCommand("select " + 
                    "col.[name], col.xtype, col.isnullable from sysobjects" + 
                    " obj inner join syscolumns col on obj.id = col.id where obj.name = '" + 
                    tableName + "'");
                    
                if(reader.Length > 0)
                {
                    foreach(object rObj in reader)
                    {
                        object[] readerObj = (object[])rObj;
                        columnNameDB.Add((string)readerObj[0]);
                        switch ((byte)readerObj[1])
                        {
                            case 36:
                                columnTypeDB.Add("uniqueidentifier");
                                break;
                            case 56:
                                columnTypeDB.Add("int");
                                break;
                            case 61:
                                columnTypeDB.Add("datetime");
                                break;
                            case 62:
                                columnTypeDB.Add("float");
                                break;
                            case 104:
                                columnTypeDB.Add("bit");
                                break;
                            case 167:
                                columnTypeDB.Add("varchar");
                                break;
                            default:
                                throw new Exception("Returned column type not recognised");
                        }
                        switch ((Int32)readerObj[2])
                        {
                            case 0:
                                nullableDB.Add(false);
                                break;
                            case 1:
                                nullableDB.Add(true);
                                break;
                        }
                    }
                }
                else { returnString.Add("Table \"" + tableName + "\" doesn't exist."); }

                foreach(ColumnDefinition col in columns)
                {
                    if (columnNameDB.Contains(col.Name))
                    {
                        int index = columnNameDB.IndexOf(col.Name);
                        if (!columnTypeDB[index].Equals(col.GetTypeInDBType()))
                            returnString.Add("Table \"" + tableName + "\": Column \"" + col.Name +
                                "\" type is incorrect: Should be \"" + col.GetTypeInDBType() + "\", is \"" + columnTypeDB[index] + "\"");
                        if (!nullableDB[index].Equals(col.Nullable))
                            returnString.Add("Table \"" + tableName + "\": Column \"" + col.Name +
                                "\": nullable should be \"" + col.Nullable + "\", is \"" + nullableDB[index] + "\"");
                    }
                    else 
                    {
                        returnString.Add("Table \"" + tableName + "\": Column \"" + col.Name + "\" doesn't exist.");
                    }
                }
            }
            if (returnString.Count > 0) { return returnString.ToArray(); }
            else { return null; }
        }
        #endregion

        #region Update
        /// <summary>
        /// Returns the sql queries that must be run inorder to ensure the database matches the model
        /// </summary>
        /// <param name="xmlPath">Path to XML model file</param>
        /// <returns>String array containing the queries</returns>
        public string[] GetUpdateScriptDatabaseAgainstModel() 
        {
            List<string> returnString = new List<string> { };
            List<string> newForeignKeys = new List<string> { };
            
            if (xmlTools == null) { throw new NullReferenceException("xmlReader object is null"); }

            TableDefinition[] tables = xmlTools.Tables;

            if (!CheckDatabaseExists(xmlTools.DatabaseName))
            {
                return new string[] {CreateDatabase()};
            }

            returnString.AddRange(CreateTables());

            foreach (TableDefinition tb in tables)
            {
                string tableName = tb.Name;
                ColumnDefinition[] columns = tb.ColumnArray;

                List<string> columnNameDB = new List<string>{};
                List<string> columnTypeDB = new List<string>{};
                List<bool> nullableDB = new List<bool>{};
                List<int> lengthDB = new List<int>{};
            
                object[] reader = GlobalObjectsSQL.ExecuteReaderQueryCommand("select " +
                    "col.[name], col.xtype, col.isnullable, col.length from sysobjects" +
                    " obj inner join syscolumns col on obj.id = col.id where obj.name = '" +
                    tableName + "'");
                if (reader.Length > 0)
                {
                    foreach (object rObj in reader)
                    {
                        object[] readerObj = (object[])rObj;
                        columnNameDB.Add((string)readerObj[0]);
                        switch ((byte)readerObj[1])
                        {
                            case 36:
                                columnTypeDB.Add("uniqueidentifier");
                                break;
                            case 56:
                                columnTypeDB.Add("int");
                                break;
                            case 61:
                                columnTypeDB.Add("datetime");
                                break;
                            case 62:
                                columnTypeDB.Add("float");
                                break;
                            case 104:
                                columnTypeDB.Add("bit");
                                break;
                            case 167:
                                columnTypeDB.Add("varchar");
                                break;
                            default:
                                throw new Exception("Returned column type not recognised");
                        }
                        switch ((Int32)readerObj[2])
                        {
                            case 0:
                                nullableDB.Add(false);
                                break;
                            case 1:
                                nullableDB.Add(true);
                                break;
                        }
                        if((short)readerObj[3] > 0 && (short)readerObj[3] < 256)
                            lengthDB.Add(Convert.ToInt32((short)readerObj[3]));
                        else
                            lengthDB.Add(-1);
                    }
                    foreach (ColumnDefinition col in columns)
                    {
                        if (columnNameDB.Contains(col.Name))
                        {
                            int index = columnNameDB.IndexOf(col.Name);
                            if (!columnTypeDB[index].Equals(col.GetTypeInDBType()) || !nullableDB[index].Equals(col.Nullable) ||
                                (col.Type == ColumnType.Varchar && lengthDB[index] != col.Length))
                            {
                                returnString.Add("Alter table [dbo].[" + tableName + "] Alter Column " + col.Name + " " +
                                    col.GetTypeInDBType() + (col.Type == ColumnType.Varchar ? "(" + col.Length + ")" : "") +
                                    " " + (col.Nullable ? "NULL" : "NOT NULL"));

                                if (col.DefaultValue != null)
                                {
                                    if (GlobalObjectsSQL.CheckObjectExists("DF_" + tableName + "_" + col.Name, "DEFAULT_CONSTRAINT"))
                                        returnString.Add("Alter table [" + tableName + "] DROP CONSTRAINT [DF_" + tableName + "_" + col.Name + "]");

                                    returnString.Add("Alter table [" + tableName + "] ADD  CONSTRAINT [DF_" +
                                        tableName + "_" + col.Name + "] DEFAULT (" + (col.Type != ColumnType.GUID ? "'" : "")
                                        + col.DefaultValue + (col.Type != ColumnType.GUID ? "'" : "") + ") for [" + col.Name + "]");
                                }
                            }
                        }
                        else
                        {
                            returnString.Add("Alter table [dbo].[" + tableName + "] Add " + col.Name + " " +
                                    col.GetTypeInDBType() + (col.Type == ColumnType.Varchar ? "(" + col.Length + ")" : "") +
                                    " " + (col.Nullable ? "NULL" : "NOT NULL"));
                            if (col.DefaultValue != null)
                            {
                                returnString.Add("Alter table [" + tableName + "] ADD  CONSTRAINT [DF_" +
                                    tableName + "_" + col.Name + "] DEFAULT (" + (col.Type != ColumnType.GUID ? "'" : "")
                                    + col.DefaultValue + (col.Type != ColumnType.GUID ? "'" : "") + ") for [" + col.Name + "]");
                            }
                            if (col.ForeignKey != null)
                            {
                                newForeignKeys.Add("ALTER TABLE [dbo].[" + tableName +
                                            "] WITH CHECK ADD  CONSTRAINT [FK_" + tableName + "_" + col.Name +
                                            "_" + col.ForeignKey.Table + "_" + col.ForeignKey.Column + "] FOREIGN KEY([" + col.Name + "])" +
                                            " REFERENCES [dbo].[" + col.ForeignKey.Table + "] ([" + col.ForeignKey.Column + "])" +
                                            " GO " + "ALTER TABLE [dbo].[" + tableName +
                                            "] CHECK CONSTRAINT [FK_" + tableName + "_" + col.Name +
                                            "_" + col.ForeignKey.Table + "_" + col.ForeignKey.Column + "]");
                            }
                        }
                    }
                }

            }
            foreach (string obj in newForeignKeys)
                returnString.Add(obj);    
            if (returnString.Count > 0) { return returnString.ToArray(); }
            else { return null; }
        }
        /// <summary>
        /// Updates an existing database to match the model
        /// </summary>
        /// <param name="xmlPath">Path to XML model file</param>
        public void UpdateDatabaseAgainstModel() 
        {
            if (!CheckDatabaseExists(xmlTools.DatabaseName))
            {
                GlobalObjectsSQL.DatabaseName = null;
                GlobalObjectsSQL.ExecuteNonQueryCommand(CreateDatabase());
                GlobalObjectsSQL.DatabaseName = xmlTools.DatabaseName;
            }
            string[] sqlCommands = GetUpdateScriptDatabaseAgainstModel();
            if (sqlCommands == null) { return; }
            foreach (string sqlCommand in sqlCommands)
            {
                GlobalObjectsSQL.ExecuteNonQueryCommand(sqlCommand);
            }
        }
        #endregion

        #region Data Providers
        public void SetProviders(Provider[] value)
        {
            xmlTools.DataProviders = value;
        }

        public Provider[] GetProviders()
        {
            return xmlTools.DataProviders;
        }

        public Provider GetDefaultProvider()
        {
            foreach (Provider pro in xmlTools.DataProviders)
            {
                if (pro.IsDefault)
                    return pro;
            }
            if (xmlTools.DataProviders.Length > 0)
                return xmlTools.DataProviders[0];
            else
                return null;
        }
        #endregion

        #region Options
        public bool Options_Recursive
        {
            set { xmlTools.Option_Recursive = value; }
            get { return xmlTools.Option_Recursive; }
        }
        public EmailServer Options_EmailServer
        {
            get { return xmlTools.Option_EmailServer; }
            set { xmlTools.Option_EmailServer = value; }
        }
        public bool Options_LogSql
        {
            set { xmlTools.Option_LogSql = value; }
            get { return xmlTools.Option_LogSql; }
        }
        public bool Option_Save_Auto
        {
            set { xmlTools.Option_Save_Auto = value; }
            get { return xmlTools.Option_Save_Auto; }
        }
        public bool Option_Save_Request
        {
            set { xmlTools.Option_Save_Request = value; }
            get { return xmlTools.Option_Save_Request; }
        }
        public void SaveOptions()
        {
            xmlTools.SaveOptions();
        }
        #endregion
    }
}
